﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;

/*
 返回值约定：
 1：执行成功
 0：执行失败
     */
namespace MyDLL
{
    /// <summary>
    /// 标签数据库管理类
    /// </summary>
    public class MySqlite
    {
        private SQLiteConnection conn;  //数据库连接对象
        private SQLiteCommand cmd;  //sql命令
        /// <summary>
        /// 数据库文件路径
        /// </summary>
        public string DB_Path;

#region 数据库管理 
        /// <summary>
        /// 创建并打开数据库
        /// </summary>
        /// <param name="DB_Path">数据库文件路径。例子："Test.db" "E:/Test.ddd" "./EEE.DB"</param>
        /// <returns></returns>
        public int Create_Open_DB(string DB_Path)
        {                        
            Open_DB(DB_Path);
            Create_Tables();
            return 1;
        }

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <param name="DB_Path">数据库文件路径。例子："Test.db" "E:/Test.ddd" "./EEE.DB"</param>
        /// <returns></returns>
        public int Open_DB(string DB_Path)
        {
            this.DB_Path = DB_Path;
            if (conn != null)
                conn.Close();
            conn = new SQLiteConnection("Data Source=" + DB_Path);
            conn.Open();
            if (cmd == null)
                cmd = conn.CreateCommand();
            return 1;
        }

        /// <summary>
        /// 清空数据库中的全部数据
        /// </summary>
        /// <returns></returns>
        public int Clear_DB()
        {
            return 1;
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <returns></returns>
        public int Close_DB()
        {
            if (conn != null)
                conn.Close();
            return 1;
        }
        /*~MySqlite()   //python调用包含析构函数的dll会报错，原因未知
        {
            Close_DB();
        }*/

        /// <summary>
        /// 创建表结构
        /// </summary>
        /// <returns></returns>
        private int Create_Tables()
        {
            cmd.CommandText= "CREATE TABLE if not exists \"Target\"(\"ID\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"Name\"  TEXT,\"Comment\"  TEXT);";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE if not exists \"Tag\" (\"ID\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"Name\"  TEXT,\"Comment\"  TEXT,\"Refer\"  INTEGER); ";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE if not exists \"Map_Target_Tag\" (\"ID\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"TargetID\"  INTEGER,\"TagID\"  INTEGER); ";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE if not exists \"Map_Tag_Tag\" (ID  INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL ,ParentID INTEGER,ChildID INTEGER);";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE VIEW if not exists overview AS select a.Name,group_concat(b.Name,\"；\") from Target a,Map_Target_Tag m,Tag b where a.ID =m.TargetID and m.TagId =b.ID group by m.TargetID; ";
            cmd.ExecuteNonQuery();
            return 1;
        }

        /// <summary>
        /// 更新Tag表中Refer（被引用次数）字段值
        /// </summary>
        public void ReFresh_Tag_Refer()
        {
            string sql;
            SQLiteTransaction tr = conn.BeginTransaction();
            //创建一个临时表Refer_count
            sql = "create TEMPORARY table Refer_count (TagID int,Refer int);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            //在Map_Target_Tag统计标签数据，并将结果保存到临时表Refer_count中
            sql = "insert into Refer_count select TagID,count(*) from Map_Target_Tag group by TagID;";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            //将临时表中的引用数量保存在Tag表中
            sql = "update Tag set Refer=(select Refer from Refer_count where Tag.ID = Refer_count.TagID);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            //将没有被引用的标签的Refer设置为0
            sql = "update Tag set Refer=0 where Refer is null;";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            //删除临时表
            sql = "drop table temp.Refer_count;";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            tr.Commit();
        }
#endregion

#region 数据库记录-Target-增删改、查询备注
        /// <summary>
        /// 添加一条Target记录
        /// </summary>
        /// <param name="Name">文件名。例如:"1.txt""test.sln"等</param>
        /// <param name="Comment">备注</param>
        /// <returns>1:执行成功。 0:文件名早已存在</returns>
        public int Add_Target(string Name,string Comment)
        {
            //需要先检测记录是否存在
            if (Get_Target_ID_By_Target_Name(Name) != -1)
                return 0;
            cmd.CommandText = "insert or replace into Target(Name,Comment) values (@name,@comment);";
            cmd.Parameters.Add(new SQLiteParameter("@name", Name));
            cmd.Parameters.Add(new SQLiteParameter("@comment", Comment));
            cmd.ExecuteNonQuery();
            return 1;
        }

        /// <summary>
        /// 删除一条Target记录
        /// </summary>
        /// <param name="Name">标签名</param>
        /// <returns></returns>
        public int Delete_Target_by_Name(string Name)
        {
            //检测记录是否存在
            int target_id = Get_Target_ID_By_Target_Name(Name);
            if (target_id == -1)
                return 0;
            Delete_Target_by_ID(target_id);
            Delete_Map_Target_Tag_by_TargetID(target_id);
            return 1;
        }

        /// <summary>
        /// 在Target表中，根据Target的id删除对应记录。注意：此函数没有检测id是否存在
        /// </summary>
        /// <param name="id">Target的ID字段值</param>
        /// <returns></returns>
        private int Delete_Target_by_ID(int id)
        {
            cmd.CommandText = "delete from Target where ID =" + id.ToString();
            cmd.ExecuteNonQuery();
            return 1;
        }
        //修改目标的备注
        public int Update_Target_Comment(string Target_Name,string New_Comment)
        {
            cmd.CommandText = "update Target set Comment=@new_comment where Name=@name;";
            cmd.Parameters.Add(new SQLiteParameter("@new_comment", New_Comment));
            cmd.Parameters.Add(new SQLiteParameter("@name", Target_Name));
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 修改目标的名字
        /// </summary>
        /// <param name="old_Name"></param>
        /// <param name="new_Name"></param>
        /// <returns></returns>
        public int Update_Target_Name(string old_Name,string new_Name)
        {
            cmd.CommandText = "update Target set Name=@new_name where Name=@old_name;";
            cmd.Parameters.Add(new SQLiteParameter("@new_name", new_Name));
            cmd.Parameters.Add(new SQLiteParameter("@old_name", old_Name));
            cmd.ExecuteNonQuery();
            return 1;
        }
        //查询备注
        public string Get_Target_Comment(string Target_Name)
        {
            cmd.CommandText = "select Comment from Target where Name=@name";
            cmd.Parameters.Add(new SQLiteParameter("@name", Target_Name));
            List<string> temp = get_list_string();
            if (temp == null)
                return null;//不存在时返回null
            else
                return temp[0];
        }
        /// <summary>
        /// 检测文件或文件夹是否存在。
        /// 1:目标存在。0:目标不存在
        /// </summary>
        /// <param name="Target_Name">目标名</param>
        /// <returns></returns>
        public int Target_is_Exist(string Target_Name)
        {
            if (Get_Target_ID_By_Target_Name(Target_Name) != -1)
                return 1;
            return 0;
        }
        /// <summary>
        /// 获取指定记录的id，如果不存在则返回-1
        /// </summary>
        /// <param name="Target_Name">Target名</param>
        /// <returns>-1：没有此记录。 其他整数：指定记录的id</returns>
        public int Get_Target_ID_By_Target_Name(string Target_Name)
        {
            cmd.CommandText = "select ID from Target where Name=@name";
            cmd.Parameters.Add(new SQLiteParameter("@name", Target_Name));
            SQLiteDataReader reader = cmd.ExecuteReader();
            int result=-1;            
            if (reader.HasRows)
            {
                reader.Read();
                result = reader.GetInt32(0);
            }
            reader.Close();
            return result;  //如果找不到就返回-1
        }

        /// <summary>
        /// 根据TargetID返回Target记录
        /// </summary>
        /// <param name="Target_ID"></param>
        /// <returns>一条Target记录</returns>
        public List<string> Get_Target_By_ID(int Target_ID)
        {
            cmd.CommandText = "select * from Target where ID="+Target_ID.ToString();
            return get_list_string();
        }
        #endregion

#region 数据库记录-Tag
        /// <summary>
        /// 添加标签
        /// </summary>
        /// <param name="Tag_Name">标签名</param>
        /// <param name="Comment">备注</param>
        /// <returns></returns>
        public int Add_Tag(string Tag_Name,string Comment)
        {
            if (Get_Tag_ID_By_Tag_Name(Tag_Name) != -1)
                return 0;
            cmd.CommandText = "INSERT INTO Tag(Name,Comment) VALUES (@name,@comment)";
            cmd.Parameters.Add(new SQLiteParameter("@name", Tag_Name));
            cmd.Parameters.Add(new SQLiteParameter("@comment", Comment));
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 删除标签
        /// </summary>
        /// <param name="Tag_Name">标签名</param>
        /// <returns>执行成功返回1。标签不存在返回0,</returns>
        public int Delete_Tag(string Tag_Name)
        {
            int tag_id = Get_Tag_ID_By_Tag_Name(Tag_Name);
            if (tag_id == -1)
                return 0;            
            Delete_Map_Tag_Tag_by_ParentID(tag_id);
            Delete_Map_Tag_Tag_by_ChildID(tag_id);
            return 1;
        }
        /// <summary>
        /// 修改标签名字
        /// </summary>
        /// <param name="old_Name"></param>
        /// <param name="new_Name"></param>
        /// <returns></returns>
        public int Update_Tag_Name(string old_Name,string new_Name)
        {
            return 1;
        }

        /// <summary>
        /// 检测标签是否存在。存在则返回1，不存在则返回0。
        /// </summary>
        /// <param name="Tag_Name">标签名</param>
        /// <returns></returns>
        public int Tag_Is_Exist(string Tag_Name)
        {
            if (Get_Tag_ID_By_Tag_Name(Tag_Name) == -1)
                return 0;
            return 1;
        }
        
        /// <summary>
        /// 根据标签名搜索标签
        /// </summary>
        /// <param name="search_type">"exact":精准查找。"fuzzy":模糊查找</param>
        /// <param name="tag_pattern">用于匹配的语句。若用精准查找则为标签名，若用模糊查找则是sqlite3模糊搜索条件表达式</param>
        /// <param name="limit">最多返回多少个记录，是sql语句中的limit</param>
        /// <param name="offset">结果集起始位置，是sql语句中的offset</param>
        /// <returns>符合条件的标签的ID</returns>
        public List<int> Find_Tag_By_Name(string search_type, string tag_pattern, int limit = -1, int offset = -1)
        {
            string sql = "select ID from Tag where Name ";
            switch(search_type)
            {                
                case "fuzzy":
                    sql += "like ";
                    break;
                case "exact":
                default:
                    sql += "= ";
                    break;

            }
            sql += "@tag_pattern ";
            if (offset != -1 && limit != -1)
            {
                sql += "limit " + limit.ToString() + " offset " + offset.ToString();
            }
            sql += ";";
            ///sql=sql.Replace("@tag_pattern", "\""+tag_pattern+ "\"");
            cmd.CommandText = sql;
            cmd.Parameters.Add(new SQLiteParameter("@tag_pattern", tag_pattern));  
            SQLiteDataReader reader = cmd.ExecuteReader();
            List<int> result = new List<int>();            
            if (reader.HasRows)
            {
                while(reader.Read())
                    result.Add(reader.GetInt32(0));
            }
            else
            {
                result.Add(-1);
            }
            reader.Close();
            return result;
        }
        /// <summary>
        /// 获取指定标签的ID，若不存在则返回-1
        /// </summary>
        /// <param name="Tag_Name"></param>
        /// <returns>-1：没有指定标签。其他整数：指定标签的id</returns>
        public int Get_Tag_ID_By_Tag_Name(string Tag_Name)
        {
            cmd.CommandText = "select ID from Tag where Name=@name";
            cmd.Parameters.Add(new SQLiteParameter("@name", Tag_Name));
            SQLiteDataReader reader = cmd.ExecuteReader();
            int result = -1;
            if (reader.HasRows)
            {
                reader.Read();
                result = reader.GetInt32(0);
            }
            reader.Close();
            return result;
        }
        /// <summary>
        /// 根据标签id返回记录
        /// </summary>
        /// <param name="id">标签id</param>
        /// <returns>有则返回一条记录，无则返回null</returns>
        public List<string> Get_Tag_By_ID(int id)
        {
            cmd.CommandText = "select * from Tag where id=@id";
            cmd.Parameters.Add(new SQLiteParameter("@id", id));
            SQLiteDataReader reader = cmd.ExecuteReader();
            List<string> result = new List<string>();
            if (reader.HasRows)
            {
                reader.Read();
                for(int i=0;i<reader.FieldCount;i++)
                {
                    result.Add(reader.GetValue(i).ToString());
                }
            }
            else
            {
                return null;
            }
            reader.Close();
            return result;
        }
        /// <summary>
        /// 获取所有标签
        /// </summary>
        /// <param name="order_by_type">按照引用数量排序。asc:升序,desc:降序</param>
        /// <param name="limit">最多返回多少个记录，是sql语句中的limit</param>
        /// <param name="offset">结果集起始位置，是sql语句中的offset</param>
        /// <returns></returns>
        public List<int> Get_All_Tag_ID(string order_by_type = "", int limit = -1, int offset = -1)
        {
            string sql = "select ID from Tag ";
            if(order_by_type!="")
            {
                sql += " order by Refer " + order_by_type;
            }
            if(offset!=-1&&limit!=-1)
            {
                sql += " limit " + limit.ToString() + " offset " + offset.ToString();
            }
            sql += ";";
            cmd.CommandText = sql;
            SQLiteDataReader reader = cmd.ExecuteReader();
            List<int> result = new List<int>();
            if (reader.HasRows)
            {
                while (reader.Read())
                    result.Add(reader.GetInt32(0));
            }
            else
            {
                result.Add(-1);
            }
            reader.Close();
            return result;
        }
        #endregion

#region 数据库记录-映射关系Target_Tag-增删改查
        /// <summary>
        /// 添加“Target-标签”关系
        /// </summary>
        /// <param name="Target_Name">Target名</param>
        /// <param name="Tag_Name">标签名</param>
        /// <returns></returns>
        public int Add_Map_Target_Tag(string Target_Name,string Tag_Name)
        {
            //获取双方的id
            int target_id = Get_Tag_ID_By_Tag_Name(Target_Name);
            if (target_id == -1)
            {
                Add_Target(Target_Name, "");    //！：可优化
                target_id = Get_Target_ID_By_Target_Name(Target_Name);
            }                        
            int tag_id = Get_Tag_ID_By_Tag_Name(Tag_Name);
            if (tag_id == -1)
            {
                Add_Tag(Tag_Name, "");  //如果已经存在则这两条无效
                tag_id = Get_Tag_ID_By_Tag_Name(Tag_Name);
            }
            //检测映射关系是否已经存在
            //if (Get_Map_Target_Tag_By_TT_ID(target_id, tag_id)[0] != -1)
            if (Get_Map_Target_Tag_By_TT_ID(target_id, tag_id)!=null)
                return 0;
            cmd.CommandText = "INSERT INTO Map_Target_Tag(TargetID,TagID) VALUES (@target_id,@tag_id)";
            cmd.Parameters.Add(new SQLiteParameter("@target_id", target_id));
            cmd.Parameters.Add(new SQLiteParameter("@tag_id", tag_id));
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 删除“Target-标签”关系。Target、标签或者映射关系不存在都会返回0。
        /// </summary>
        /// <param name="Target_Name">Target名</param>
        /// <param name="Tag_Name">标签名</param>
        /// <returns></returns>
        public int Delete_Map_Target_Tag(string Target_Name,string Tag_Name)
        {
            int target_id = Get_Target_ID_By_Target_Name(Target_Name);
            int tag_id = Get_Tag_ID_By_Tag_Name(Tag_Name);
            if (target_id == -1 || tag_id == -1 || Get_Map_Target_Tag_By_TT_ID(target_id, tag_id)[0] == -1)
                return 0;         
            Delete_Map_Target_Tag_by_ID(target_id, tag_id);
            return 1;
        }
        /// <summary>
        /// 返回Target-Tag映射关系的id值。根据参数的不同可以返回包含Target_id或Tag_id的所有记录或一条指定的Target-Tag记录。没有则返回-1
        /// </summary>
        /// <param name="Target_id">Target的id。未指定时该参数需填为-1，此时表示缺省</param>
        /// <param name="Tag_id">标签的id。未指定时该参数需填为-1，此时表示缺省</param>
        /// <returns>记录存在则返回ID。不存在则返回一个-1</returns>
        public List<int> Get_Map_Target_Tag_By_TT_ID(int Target_id,int Tag_id)
        {
            if (Target_id == -1 && Tag_id == -1)
            {
                return null;
            }
            string sql = "select ID from Map_Target_Tag where ";
            if (Target_id != -1)
                sql += " TargetID=@Target_id ";

            if (Tag_id != -1)
            {
                if (Target_id != -1)
                    sql += "and ";
                sql += "TagID = @Tag_id ";
            }
            sql += ";";
            cmd.CommandText = sql;
            cmd.Parameters.Add(new SQLiteParameter("@Target_id", Target_id));
            cmd.Parameters.Add(new SQLiteParameter("@Tag_id", Tag_id));
            
            return get_list_int();

        }
        /// <summary>
        /// 在Map_Target_Tag表中根据ID返回记录的信息
        /// </summary>
        /// <param name="Map_id">指定记录的ID</param>
        /// <returns>一条记录的ID、TargetID、TagID</returns>
        public List<int> Get_Map_Target_Tag_By_Map_ID(int Map_id)
        {
            string sql = "select * from Map_Target_Tag where ID = "+Map_id;
            cmd.CommandText = sql;
            SQLiteDataReader reader = cmd.ExecuteReader();
            List<int> result = new List<int>();
            if (reader.HasRows)
            {
                result.Add(reader.GetInt32(0));
                result.Add(reader.GetInt32(1));
                result.Add(reader.GetInt32(2));
            }
            else
            {
                result.Add(-1);
            }
            reader.Close();
            return result;
        }
#endregion

#region 数据库记录-Tag_Tag映射关系-增删改
        /// <summary>
        /// 添加“标签-标签”关系
        /// </summary>
        /// <param name="Parent_Tag">父标签的名称</param>
        /// <param name="Child_Tag">子标签的名称</param>
        /// <returns></returns>
        public int Add_Map_Tag_Tag(string Parent_Tag,string Child_Tag)
        {
            //直接添加标签，如果有重复则不会添加。
            Add_Tag(Parent_Tag,"");
            Add_Tag(Child_Tag,"");
            //获取两个标签的ID
            int Parent = Get_Tag_ID_By_Tag_Name(Parent_Tag);
            int Child = Get_Tag_ID_By_Tag_Name(Child_Tag);
            //查询映射关系
            List<int> result = Get_Map_Tag_Tag_by_ID(Parent, Child);
            if (result != null)//如果关系已经存在
                return 0;
            //记录映射关系
            cmd.CommandText = "INSERT OR IGNORE INTO Map_Tag_Tag (ParentID,ChildID) VALUES (@Parent,@Child)";
            cmd.Parameters.Add(new SQLiteParameter("@Parent", Parent));
            cmd.Parameters.Add(new SQLiteParameter("@Child", Child));
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 删除“标签-标签”关系
        /// </summary>
        /// <param name="Parent_Name">父标签名字</param>
        /// <param name="Child_Name">子标签名字</param>
        /// <returns>成功返回1，标签不存在返回0</returns>
        public int Delete_Map_Tag_Tag(string Parent_Name,string Child_Name)
        {
            int parent_id = Get_Target_ID_By_Target_Name(Parent_Name);
            int child_id = Get_Target_ID_By_Target_Name(Child_Name);
            if (parent_id == -1 || child_id == -1)
                return 0;
            Delete_Map_Tag_Tag_by_ID(parent_id, child_id);
            return 1;
        }

        /// <summary>
        /// 在Map_Tag_Tag表中，根据ParentID删除所有相关记录。注意：本函数没有检测ParentID是否存在。
        /// </summary>
        /// <param name="parent_id">父标签的id值</param>
        /// <returns></returns>
        public int Delete_Map_Tag_Tag_by_ParentID(int parent_id)
        {
            cmd.CommandText = "delete from Map_Tag_Tag where ParentID =" + parent_id.ToString();
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 在Map_Tag_Tag表中，根据ChildID删除相关记录。注意：本函数没有检测ChildID是否存在。
        /// </summary>
        /// <param name="child_id">子标签的id值</param>
        /// <returns></returns>
        public int Delete_Map_Tag_Tag_by_ChildID(int child_id)
        {
            cmd.CommandText = "delete from Map_Tag_Tag where ChildID =" + child_id.ToString();
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 在Map_Tag_Tag中，根据父标签id和子标签id删除一条记录。注意：此函数没有检测id是否存在。
        /// </summary>
        /// <param name="parent_id">父标签id值</param>
        /// <param name="child_id">子标签id值</param>
        /// <returns></returns>
        public int Delete_Map_Tag_Tag_by_ID(int parent_id,int child_id)
        {
            cmd.CommandText = "delete from Map_Tag_Tag where ParentID= "+parent_id.ToString()+" ChildID =" + child_id.ToString();
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 根据TargetID删除“Target-标签”的映射关系。注意，本函数没有检测TargetID是否存在。
        /// </summary>
        /// <param name="target_id">Target的id</param>
        /// <returns></returns>
        private int Delete_Map_Target_Tag_by_TargetID(int target_id)
        {
            cmd.CommandText = "delete from Map_Target_Tag where TargetID =" + target_id.ToString();
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 根据标签ID删除“Target-标签”的映射关系。注意，本函数没有检测标签ID是否存在。
        /// </summary>
        /// <param name="target_id">Target的id</param>
        /// <returns></returns>
        public int Delete_Map_Target_Tag_by_TagID(int tag_id)
        {
            cmd.CommandText = "delete from Map_Target_Tag where TagID =" + tag_id.ToString();
            cmd.ExecuteNonQuery();
            return 1;
        }
        /// <summary>
        /// 根据TargetID、标签ID删除“Target-标签”的映射关系。注意，本函数没有检测标签ID是否存在。
        /// </summary>
        /// <param name="target_id">TargetID</param>
        /// <param name="tag_id">标签ID</param>
        /// <returns></returns>
        private int Delete_Map_Target_Tag_by_ID(int target_id,int tag_id)
        {
            cmd.CommandText = "delete from Map_Target_Tag where TargentID = " + target_id.ToString() + " TagID =" + tag_id.ToString();
            cmd.ExecuteNonQuery();
            return 1;
        }
        #endregion

#region Tag_Tag-查
        /// <summary>
        /// 根据父标签id或子标签id查询相关id。
        /// </summary>
        /// <returns>参数只有一方有效时，返回另一方的id。两者同时有效时，返回 关系 的id。没有则返回null。</returns>
        public List<int> Get_Map_Tag_Tag_by_ID(int parent_id=-1,int child_id=-1)
        {
            string sql = "";
            sql = "select #result from Map_Tag_Tag where #cond ;";

            if (parent_id == -1 && child_id == -1)  //如果都是无效参数
                return null;
            else if (parent_id == -1)   //如果parent_id是无效参数，那么此时child_id不是无效参数
            {
                sql = sql.Replace("#result", "ParentID");
                sql = sql.Replace("#cond", "ChildID=" + child_id.ToString());
            }
            else if (child_id == -1)    //如果child_id是无效参数，那么此时parent_id不是无效参数
            {
                sql = sql.Replace("#result", "ChildID");
                sql = sql.Replace("#cond", "ParentID=" + parent_id.ToString());
            }
            else//如果都不是无效参数，返回 关系 的ID
            {
                sql = sql.Replace("#result", "ID");
                sql = sql.Replace("#cond", "ParentID=" + parent_id.ToString()+" and ChildID="+child_id.ToString());
            }
            cmd.CommandText = sql;            
            return get_list_int();
        }
        #endregion

#region 数据库记录-Target_Tag映射关系-查
        /// <summary>
        /// 返回同时包含这些标签的Target的名字。
        /// </summary>
        /// <param name="Tag_Name">标签搜索句</param>
        /// <param name="include_split">搜索句中的 标签分隔符</param>
        /// <returns>符合条件的多个Target名</returns>
        public List<string> Get_Targets_By_Tag(string Tag_Name,char include_split='+',char exclude_split='-')
        {
            string[] tags_src0 = Tag_Name.Split(include_split);
            HashSet<string> tags_src = new HashSet<string>(tags_src0);
            tags_src.Remove("");
            if (tags_src.Count == 0)
                return null;
            string sql = "select Name from Target where ID in (select TargetID from Map_Target_Tag where TagID in (select ID from Tag where Name in (#tags_temp))group by TargetID having count(*)>=@count)";
            string tags_temp="";
            //在sql语句中，创建标签的位置
            for(int i=0;i<tags_src.Count; i++)
            {
                tags_temp += "@tag" + (i + 1).ToString();
                if (i != tags_src.Count - 1)
                    tags_temp += ',';
            }
            sql=sql.Replace("#tags_temp", tags_temp);
            //向sql语句代入参数
            cmd.CommandText = sql;
            int j = 0;
            foreach(string tag in tags_src)
            {                
                cmd.Parameters.Add(new SQLiteParameter("@tag" + (j + 1).ToString(), tag));  //AddWithValue？
                j++;
            }
            cmd.Parameters.Add(new SQLiteParameter("@count", tags_src.Count));
            //查询数据库
            List<string> result = new List<string>();
            SQLiteDataReader reader = cmd.ExecuteReader();   
            
            if(reader.HasRows)
            {
                while(reader.Read())
                    result.Add(reader.GetString(0));
            }
            reader.Close();            
            
            return result;
        }

        /// <summary>
        /// 根据目标名称获取标签列表
        /// </summary>
        /// <param name="Target_Name">标签名称</param>
        /// <returns>目标不存在时返回null</returns>
        public List<string> Get_Tags_By_TargetName(string Target_Name)
        {
            string sql = "select Name from Tag where ID in (select TagID from Map_Target_Tag where TargetID=(select ID from Target where Name=@Name));";
            cmd.CommandText = sql;
            cmd.Parameters.Add(new SQLiteParameter("@Name", Target_Name));
            //查询数据库
            List<string> result = null;
            SQLiteDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                result = new List<string>();
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
            reader.Close();
            return result;
        }

        /// <summary>
        /// 根据TagID搜索TargetID
        /// </summary>
        /// <returns></returns>
        public List<int> Search_Targets_ID_By_Tag_ID()
        {
            return null;
        }

        /// <summary>
        /// 根据参数搜索指定TargetID
        /// </summary>
        /// <param name="include_TagName">Target必须包含的TagName</param>
        /// <param name="exclude_TagName">Target不能包含的TagName</param>
        /// <returns>符合条件的TargetID</returns>
        public List<int> Get_TargetID_By_Tag_List(List<string> include_TagName,List<string> exclude_TagName)
        {
            //找出include_TagName中对应的TargetID
            HashSet<string> include_tags_src = new HashSet<string>(include_TagName);
            include_tags_src.Remove("");            
            string sql = "select TargetID from Map_Target_Tag where TagID in (select ID from Tag where Name in (#tags_temp))group by TargetID having count(*)>=@count";
            string tags_temp = "";                      
            for (int i = 0; i < include_tags_src.Count; i++) //在sql语句中，创建标签的位置
            {
                tags_temp += "@tag" + (i + 1).ToString();
                if (i != include_tags_src.Count - 1)
                    tags_temp += ',';
            }
            sql = sql.Replace("#tags_temp", tags_temp);
            //向sql语句代入参数
            cmd.CommandText = sql;
            int j = 0;
            foreach (string tag in include_tags_src)
            {
                cmd.Parameters.Add(new SQLiteParameter("@tag" + (j + 1).ToString(), tag));
                j++;
            }
            cmd.Parameters.Add(new SQLiteParameter("@count", include_tags_src.Count));
            List<int> include_TargetID = get_list_int();

            //找出exclude_TagName中对应的TargetID
            HashSet<string> exclude_tags_src = new HashSet<string>(exclude_TagName);
            exclude_tags_src.Remove("");
            List<string> str_tagID=new List<string>();
            foreach(string tagName in exclude_TagName)  //获取TagName对应的ID
            {
                int temp_tagID = Get_Tag_ID_By_Tag_Name(tagName);
                if (temp_tagID != -1)
                    str_tagID.Add(temp_tagID.ToString());
            }            
            cmd.CommandText = "select TargetID from Map_Target_Tag where TagID in("+string.Join(",",str_tagID.ToArray())+")";
            List<int> exclude_TargetID = get_list_int();

            //搜索TargetID
            if(exclude_TargetID!=null)
                include_TargetID.RemoveAll(e=>exclude_TargetID.Contains(e));
            return include_TargetID;
        }
        #endregion

        #region 私有方法
        private List<string> get_list_string()
        {
            List<string> result = null;
            SQLiteDataReader reader = cmd.ExecuteReader();            
            if (reader.HasRows)
            {
                result = new List<string>();
                reader.Read();
                if (reader.FieldCount > 1)
                    for (int i = 0; i < reader.FieldCount; i++)
                        result.Add(reader.GetValue(i).ToString());
                else
                    do
                        result.Add(reader.GetString(0));
                    while (reader.Read());
            }
            reader.Close();
            return result;
        }
        
        private List<List<string>> get_list_list_string()
        {
            List<List<string>> result = null;
            SQLiteDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                result = new List<List<string>>();
                while (reader.Read())
                {
                    List<string> temp = new List<string>();
                        for (int i = 0; i < reader.FieldCount; i++)
                        temp.Add(reader.GetValue(i).ToString());
                    result.Add(temp);
                }
            }            
            reader.Close();
            return result;
        }

        private List<int> get_list_int()
        {
            List<int> result = null;
            SQLiteDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                result = new List<int>();
                while (reader.Read())
                    result.Add(reader.GetInt32(0));
            }
            reader.Close();
            return result;
        }
        #endregion
    }
}
